package dang.dao.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dang.dao.CategoryDao;
import dang.entity.Category;
import dang.exception.DangException;

public class CategoryDaoImpl extends DangDaoImpl implements CategoryDao {

	@Override
	public List<Category> findAll() throws DangException {
		String sql="select * from d_category";
		List<Category> cats=new ArrayList<Category>();
		try {
			PreparedStatement ps=getConnection().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Category cat=new Category();
				cat.setDescription(rs.getString("description"));
				cat.setEn_name(rs.getString("en_name"));
				cat.setId(rs.getInt("id"));
				cat.setName(rs.getString("name"));
				cat.setParent_id(rs.getInt("parent_id"));
				cat.setTurn(rs.getInt("turn"));
				cats.add(cat);
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new DangException(e);
		}
		return cats;
	}

	@Override
	public List<Category> findByParentId(int parentId) throws DangException {
		StringBuffer sql = new StringBuffer();
		sql.append(" select dc.*, count(dcp.product_id) as pnum ");
		sql.append(" from d_category dc ");
		sql.append(" 	left outer join d_category_product dcp  ");
		sql.append(" 	on (dc.id=dcp.cat_id) ");
		sql.append(" where parent_id=? ");
		sql.append(" group by dc.id ");
		
		try{
			PreparedStatement ps = getConnection().prepareStatement(sql.toString());
			ps.setInt(1, parentId);
			ResultSet rs = ps.executeQuery();
			List<Category> list = new ArrayList<Category>();
			while(rs.next()){
				Category cat = new Category();
				cat.setId(rs.getInt("id"));
				cat.setEn_name(rs.getString("en_name"));
				cat.setName(rs.getString("name"));
				cat.setTurn(rs.getInt("turn"));
				cat.setParent_id(rs.getInt("parent_id"));
				cat.setDescription(rs.getString("description"));
				cat.setPnum(rs.getInt("pnum"));
				list.add(cat);
			}
			return list;
		}catch(Exception e){
			throw new DangException(e);
		}
	}

	@Override
	public String findById(int id) throws DangException {
		String sql = "select name from d_category where id = ?";
		String name=null;
		try {
			PreparedStatement ps = getConnection().prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				name=rs.getString("name");
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new DangException(e);
		}
		
		return name;
	}

}
